#!/usr/bin/env groovy
package MergeSqlUtil

// Assumption: the sheet name is the table name.
// Assumption: The sheets first row is the table COLUMN names.

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;

import java.text.DecimalFormat;
import java.text.NumberFormat;


ArrayList<String> convertWorkbookToSQL(HSSFWorkbook workbook)
{
	ArrayList<String> sqlStatements = new ArrayList<String>();
	int sheets = workbook.getNumberOfSheets();
	for(int i = 0; i < sheets; i++)
	{
		HSSFSheet sheet = workbook.getSheetAt(i);
		ArrayList<String[]> sheetData = sheetToArrayList(sheet);
		if(sheetData.size() == 0)
		{
			continue;
		}

		String[] columns = sheetData[0]
		
		for(int j = 1; j < sheetData.size(); j++)
		{
			String result = toSQLInsertStatement(sheet.getSheetName(), sheetData[j], columns);
			sqlStatements.add(result);
		}
	}
	return sqlStatements
}

ArrayList<String> convertSheetToSQL(HSSFSheet sheet)
{
	ArrayList<String> sqlStatements = new ArrayList<String>();
	ArrayList<String[]> sheetData = sheetToArrayList(sheet);

	String[] columns = sheetData[0]
	
	for(int j = 1; j < sheetData.size(); j++)
	{
		String result = toSQLInsertStatement(sheet.getSheetName(), sheetData[j], columns);
		sqlStatements.add(result);
	}
	return sqlStatements
}

ArrayList<String[]> sheetToArrayList(HSSFSheet sheet)
{
	ArrayList<String[]> sheetData = new ArrayList<String[]>();
	
	int rowLength = 0;
	int j = 0;
	while(true)
	{
		HSSFRow row = sheet.getRow(j);
		if(row == null) 
		{
			break;
		}
		
		if(j == 0) 
		{
			rowLength = row.getLastCellNum();
		}

		String[] rowData = new String[rowLength];
		int nullCounter = 0;
		for(int k = 0; k < rowLength; k++)
		{
			HSSFCell cell = row.getCell(k);
			
			if(cell == null)
			{
				rowData[k] = "NULL";
			} else if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
			{
				Double num = cell.getNumericCellValue();
				NumberFormat nf = new DecimalFormat("0.###")
				String num2 = nf.format(num)
				rowData[k] = num2
			} else
			{
				rowData[k] = cell.toString().replaceAll("\n", "").replaceAll("\r", "");
			}
				
			if(rowData[k].equals("") || rowData[k].equals("NULL"))
			{
				nullCounter++
			}
		}
		
		j++;
		if(nullCounter != rowLength)
		{
			sheetData.add(rowData);
		} else
		{
			break;
		}
	}
	return sheetData;
}


String toSQLInsertStatement(String tableName, String[] row, String[] columns)
{
	StringBuilder sqlStmt = new StringBuilder()
	sqlStmt.append("INSERT INTO ")
	sqlStmt.append(tableName)
	sqlStmt.append(" (");
	for(int i = 0; i < columns.length; i++)
	{
		if(i > 0)
		{
			sqlStmt.append(", ")
		}
		sqlStmt.append(columns[i])
	}
	sqlStmt.append(")");
	sqlStmt.append(" VALUES (")
	for(int i = 0; i < row.size(); i++)
	{
		if(i > 0)
		{
			sqlStmt.append(", ")
		}
		if(row[i].toUpperCase().equals("NULL") || row[i].toUpperCase().equals("(NULL)"))
		{
			sqlStmt.append("NULL")
		} else if(row[i].isNumber())
		{
			sqlStmt.append(row[i])
		} else
		{
			def escSQuote = row[i].replace("'", "''")
			sqlStmt.append("'")
			sqlStmt.append(escSQuote)
			sqlStmt.append("'")
		}
		
	}
	sqlStmt.append(");")

	return sqlStmt.toString()
}

def cli = new CliBuilder( usage: "${this.class.name}.groovy [options...] [<file1> [<file2> [<file3>]...]]" )

cli.h( longOpt: 'help', 'usage information' )

def opt = cli.parse( args )

if ( opt.h )
{
    cli.usage()
    System.exit 1
}
if(opt.arguments().size() == 0)
{
	println("Please provide an input file.")
    cli.usage()
    System.exit 1
}

def files = opt.arguments()

def filename = files[0]

InputStream inp = new FileInputStream(filename);
HSSFWorkbook wb = new HSSFWorkbook(inp);
int sheets = wb.getNumberOfSheets();

Date date = new Date()
StringBuilder today = new StringBuilder()
Formatter dateform = new Formatter(today, Locale.US);
dateform.format("%tY%tm%td%tH%tM", date, date, date, date, date)
dateform.close()

for(int i = 0; i < sheets; i++)
{
	HSSFSheet sheet = wb.getSheetAt(i);
	ArrayList<String> results = convertSheetToSQL(sheet);
	println(sheet.getSheetName())
	def fileparts = filename.split("\\.")
	def output = new File(sheet.getSheetName() + "-" + today + ".sql")
	println(output.getName())
	output.write("")
	results.each { output.append("${it}\n") }
}
